﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;

namespace CrmApplication.Models
{
    public class CompanyModels
	{
		#region RegionMappingCompany

		public decimal IdCompany { get; set; }
        public string CompanyName { get; set; }

        public string Cui { get; set; }

        public decimal IdTurnover { get; set; }
		public string TurnoverName { get; set; }
		public string IdTurnover_val { get; set; }

        public decimal IdEmployee { get; set; }
		public string EmployeeName { get; set; }
		public string IdEmployee_val { get; set; }

		#endregion

		#region RegionOracleConnect
		private static OracleConnection connect;

        static CompanyModels()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }

		#endregion

		#region RegionCompanyOperations
		/// <summary>
		/// select all companies
		/// </summary>
		/// <returns></returns>
        public static List<CompanyModels> GetCompany()
        {
            string selectString = "select c.id_company,c.company_name,"
									+ " c.cui,ne.employee_name,nt.turnover_name "
                                    + " from company c "
									+ " join nomemployee ne "
									+ " on c.id_employee = ne.id_employee "
									+ " join nomturnover nt "
									+ " on c.id_turnover = nt.id_turnover "
									+ " order by company_name ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            List<CompanyModels> result = new List<CompanyModels>();
            CompanyModels result1 = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new CompanyModels();
                        result1.IdCompany= sdr.GetDecimal(0);
                        result1.CompanyName= sdr.GetString(1);
                        result1.Cui = sdr.GetString(2);                        
                        result1.EmployeeName = sdr.GetString(3);
                        result1.TurnoverName =  sdr.GetString(4);                        
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }


		/// <summary>
		/// select company by id 
		/// </summary>
		/// <param name="IdCompany"></param>
		/// <returns></returns>
        public static List<CompanyModels> GetCompanyById(decimal IdCompany)
        {
			string selectString = "select c.id_company,c.company_name,"
									+ " c.cui,c.id_turnover,c.id_employee,"
									+ " ne.employee_name,nt.turnover_name "
									+ " from company c "
									+ " join nomemployee ne "
									+ " on c.id_employee = ne.id_employee "
									+ " join nomturnover nt "
									+ " on c.id_turnover = nt.id_turnover "
									+ " where id_company =:IdCompany ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":id", IdCompany);

            List<CompanyModels> result = new List<CompanyModels>();
            CompanyModels result1 = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new CompanyModels();
                        result1.IdCompany = sdr.GetDecimal(0);
                        result1.CompanyName = sdr.GetString(1);
                        result1.Cui = sdr.GetString(2);
						result1.IdTurnover = sdr.GetDecimal(3);
						result1.IdEmployee = sdr.GetDecimal(4);                        
                        result1.IdEmployee_val= sdr.GetString(5);
						result1.IdTurnover_val = sdr.GetString(6);
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
		
        /// <summary>
        /// edit company info(update selected company database records)
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static CompanyModels EditCompany(CompanyModels company)
        {
            string selectString = "update company "
                                 + " set company_name =:CompanyName,cui=:Cui,"
								 + " id_turnover=:IdTurnover,id_employee=:nrEmployees "
                                 + " where id_company = :IdCompany";

            OracleCommand updateComm = new OracleCommand(selectString);
            updateComm.Connection = connect;
            updateComm.Parameters.Add(":CompanyName", company.CompanyName);
            updateComm.Parameters.Add(":Cui", company.Cui);
            updateComm.Parameters.Add(":IdTurnover", company.IdTurnover);
            updateComm.Parameters.Add(":nrEmployees", company.IdEmployee);
            updateComm.Parameters.Add(":IdCompany", company.IdCompany);

            CompanyModels result = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = updateComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new CompanyModels();
                        result.CompanyName = sdr.GetString(0);
                        result.Cui = sdr.GetString(1);                        
                        result.IdTurnover = sdr.GetDecimal(2);
                        result.IdEmployee = sdr.GetDecimal(3);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
		
        /// <summary>
        /// verify if company name  exists
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static CompanyModels CheckCompany(CompanyModels company)
        {
            string selectString = "select id_company,company_name "
									+ " from company where company_name =:CompanyName ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":CompanyName", company.CompanyName);

            CompanyModels result = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new CompanyModels();
                        result.IdCompany = sdr.GetDecimal(0);
                        result.CompanyName = sdr.GetString(1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
		
        /// <summary>
        /// check if company name exists(for edit)
        /// </summary>
        /// <param name="company"></param>
        /// <returns></returns>
        public static CompanyModels CheckCompanyEdit(CompanyModels company)
        {
            string selectString = "select id_company,company_name from company where company_name =:CompanyName ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":CompanyName", company.CompanyName);

            CompanyModels result = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new CompanyModels();
                        result.IdCompany = sdr.GetDecimal(0);
                        result.CompanyName = sdr.GetString(1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// add new company
        /// </summary>
        /// <param name="user"></param>
        public static void AddCompany(CompanyModels company)
        {
            string insert = "insert into company(company_name,cui,id_turnover,id_employee) " +
                            "values (:CompanyName,:Cui,:IdTurnover,:IdEmployee) ";

            OracleCommand usercomm = new OracleCommand(insert);
            usercomm.Parameters.Add("CompanyName", company.CompanyName);
            usercomm.Parameters.Add("Cui", company.Cui);
            usercomm.Parameters.Add("IdTurnover", company.IdTurnover);
			usercomm.Parameters.Add("IdEmployee", company.IdEmployee);            

            usercomm.Connection = connect;

            try
            {
                connect.Open();
                usercomm.ExecuteNonQuery();
            }
            finally
            {
                connect.Close();
            }
        }
#endregion

		#region RegionJsonArrays
		/// <summary>
        /// create company array for json
        /// </summary>
        /// <returns></returns>
        public static CompanyContainer GetCompanyArray()
        {
            CompanyModels[] data = new CompanyModels[20];
            data = CompanyModels.GetCompany().ToArray();

            CompanyContainer container = new CompanyContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
        }

		/// <summary>
		/// create json array by id
		/// </summary>
		/// <param name="IdCompany"></param>
		/// <returns></returns>
        public static CompanyContainer GetCompanyArrayById(decimal IdCompany)
        {
            CompanyModels[] data = new CompanyModels[20];
            data = CompanyModels.GetCompanyById(IdCompany).ToArray();

            CompanyContainer container = new CompanyContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
		}
		#endregion
	}

    public class CompanyContainer
    {
        public CompanyModels[] Records { get; set; }
        public int Count { get; set; }
    }
}